﻿/******************************************************************************
'Description:批产、新机、大修、检修、备件计划执行看板
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhunan
'create Date:2020.9.15
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ReportBusiness
{
    public class ZSPichanPlanLookBoardBusiness
    {
        public uMESReportOracleDAL dal = new uMESReportOracleDAL();

        //批产、大修、检修、备件 add zhunan 2020.10.13
        public DataTable GetPichanPlanInfo(Dictionary<string, string> para)
        {
            string sql = @"
SELECT u.attributevalue
FROM mfgorder mfg
LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
LEFT JOIN enginepartplan ep ON ep.mfgorderid = mfg.mfgorderid
WHERE ep.mfgorderid IS NOT NULL 
      AND trunc(mfg.factorycompletiondate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate))
";
            if (para.ContainsKey("ordertypename"))
            {
                sql += string.Format(" AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '{0}' ", para["ordertypename"]);
            }

            sql += " GROUP BY u.attributevalue";

            DataTable dtEngineList = dal.GetDataTable(sql);

            StringBuilder sbEngineList = new StringBuilder();
            sbEngineList.Append("(");

            foreach (DataRow dr in dtEngineList.Rows)
            {
                sbEngineList.AppendFormat("'{0}',",dr["attributevalue"]);
            }

            if (sbEngineList.ToString().Length > 1)
            {
                sbEngineList.Remove(sbEngineList.ToString().Length - 1, 1);
            }
            else
            {
                sbEngineList.Append("''");
            }

            sbEngineList.Append(")");

            sql = @"
SELECT mfg.mfgorderid,u.attributevalue,0 sequence,'' workflowstepname,'未排产;未排产;' status,ep.exceptiondesc
FROM mfgorder mfg
LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename='发动机'
LEFT JOIN enginepartplan ep ON ep.mfgorderid = mfg.mfgorderid
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
WHERE ep.mfgorderid IS NULL AND trunc(mfg.factorycompletiondate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate)) ";
            if (para.ContainsKey("ordertypename"))
            {
                sql += string.Format(" AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '{0}' ", para["ordertypename"]);
            }
            sql += @"
UNION ALL
SELECT temp.*
FROM
(
    SELECT ep.mfgorderid,u.attributevalue,ws.sequence,ws.workflowstepname,
           CASE WHEN MAX(ep.finishdate) IS NOT NULL 
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ' | ' || to_char(MAX(ep.finishdate),'mm/dd') || ';已完工;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) > trunc(MAX(ep.plannedfinishdate))
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';超期未完;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND jj.status = '集件预警'
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';集件预警;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND  ep.exceptiondesc IS NULL
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';已排产无问题;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND ep.exceptiondesc IS NOT NULL
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';已排产有问题;' || ep.exceptiondesc
           END status,ep.exceptiondesc
    FROM enginepartplan ep
    LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename = '发动机'
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    --查订单类型
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
    LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
    LEFT JOIN (   ---查询集件状态
                  SELECT m.mfgorderid,m.attributevalue,m.workflowstepname,CASE WHEN SUM(DECODE(m.status,'期量内未发料',1,0)) > 0 THEN '集件预警' ELSE '其他' END status
                  FROM
                  (
                      SELECT t.mfgorderid,t.attributevalue,t.workflowstepname,t.productname,
                             CASE WHEN t.today < t.startdate AND t.isQitao = 1 THEN '提前已发料'
                                  WHEN t.today < t.startdate AND t.isQitao = 0 THEN '提前未发料'
                                  WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 1 THEN '期量内发料'
                                  WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 0 THEN '期量内未发料'
                                  WHEN t.today > t.enddate AND t.isQitao = 1 THEN '超期发料'
                                  WHEN t.today > t.enddate AND t.isQitao = 0 THEN '超期未发料' 
                             END status
                      FROM
                      (
                          SELECT TRUNC((ep.plannedfinishdate - NVL(ep.priordays,1))) startdate,trunc(ep.plannedfinishdate) enddate,trunc(SYSDATE) today,
                                 wfs.workflowstepname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty,
                                 SUM(pl.Qtyrequired) Qtyrequired,CASE WHEN cmm.qty IS NOT NULL THEN 1 ELSE 0 END isQitao
                          FROM enginepartplan ep
                          LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
                          LEFT JOIN mfgorder m ON m.mfgorderid = ep.mfgorderid
                          LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename='发动机'
                          LEFT JOIN bom b ON b.workflowid = ep.workflowid
                          LEFT JOIN productmateriallistitem PL ON pl.Bomid = b.bomid
                          LEFT JOIN productbase pb1 ON pb1.productbaseid = pl.Productbaseid
                          LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,pl.Productid)
                          LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                          LEFT JOIN ( SELECT cm.mfgordername,cm.productname,SUM(NVL(cm.qty,0)) qty 
                                      FROM Collecterpmaterial cm 
                                      WHERE cm.qty > 0 
                                      GROUP BY cm.mfgordername,cm.productname ) cmm ON cmm.mfgordername = m.mfgordername AND cmm.productname = pb.productname
                          WHERE pl.Bigsmallpart = '大零件' AND wfs.sequence < 8 ";
            sql += string.Format(" AND u.attributevalue IN {0} ",sbEngineList.ToString());

            sql += @"
                          GROUP BY ep.plannedfinishdate,ep.priordays,wfs.workflowstepname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty
                      ) t 
                  ) m
                  GROUP BY m.mfgorderid,m.attributevalue,m.workflowstepname
                  --集件状态
              ) jj ON jj.mfgorderid = ep.mfgorderid AND jj.workflowstepname = ws.workflowstepname
    WHERE p.productid = ep.productid AND ws.sequence < 13
          AND trunc(mfg.factorycompletiondate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate)) ";

            if (para.ContainsKey("ordertypename"))
            {
                sql += string.Format(" AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '{0}' ", para["ordertypename"]);
            }

            sql += @"
    GROUP BY ep.mfgorderid,u.attributevalue,ws.sequence,ws.workflowstepname,ep.priordays,ep.exceptiondesc,jj.status,ep.exceptiondesc
    ORDER BY u.attributevalue,ws.sequence
) temp
";
            DataTable dtQuery = dal.GetDataTable(sql);

            DataTable dtResult = dtQuery.DefaultView.ToTable(true, "attributevalue");

            dtResult.Columns.Add("workflowstepname");
            dtResult.Columns.Add("yicijijian");//一次集件
            dtResult.Columns.Add("yicibuzhuang");//一次部装
            dtResult.Columns.Add("yicichuanzhuang");//一次传装
            dtResult.Columns.Add("yicizongzhuang");//一次总装
            dtResult.Columns.Add("gongchangshiche");//工厂试车
            dtResult.Columns.Add("fenjie");//分解
            dtResult.Columns.Add("gujian");//故检
            dtResult.Columns.Add("ercijijian");//二次集件
            dtResult.Columns.Add("ercibuzhuang");//二次部装
            dtResult.Columns.Add("ercichuanzhuang");//二次传装
            dtResult.Columns.Add("ercizongzhuang");//二次总装
            dtResult.Columns.Add("jianyanshiche");//检验试车

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("attributevalue = '{0}'", dr["attributevalue"].ToString());
                DataRow[] drList = dtQuery.Select(filter);

                dr["workflowstepname"] = "排产日期 | 报工日期";

                if (drList.Length == 1)
                {
                    for (int i = 0; i < 12; i++)
                    {
                        dr[2 + i] = "未排产;未排产";
                    }
                }
                else
                {
                    for (int j = 0; j < drList.Length; j++)
                    {
                        dr[2 + j] = drList[j]["status"];
                    }
                }
            }

            return dtResult;
        }

        //新机 add zhunan 2020.10.13
        public DataTable GetXinjiPlanInfo(Dictionary<string, string> para)
        {
            string sql = @"
SELECT u.attributevalue
FROM mfgorder mfg
LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename='发动机'
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
LEFT JOIN enginepartplan ep ON ep.mfgorderid = mfg.mfgorderid
WHERE ep.mfgorderid IS NOT NULL 
      AND trunc(mfg.factorycompletiondate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate))
      AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'
GROUP BY u.attributevalue
";
            DataTable dtEngineList = dal.GetDataTable(sql);

            StringBuilder sbEngineList = new StringBuilder();
            sbEngineList.Append("(");

            foreach (DataRow dr in dtEngineList.Rows)
            {
                sbEngineList.AppendFormat("'{0}',", dr["attributevalue"]);
            }

            if (sbEngineList.ToString().Length > 1)
            {
                sbEngineList.Remove(sbEngineList.ToString().Length - 1, 1);
            }
            else
            {
                sbEngineList.Append("''");
            }

            sbEngineList.Append(")");

            sql = @"
SELECT mfg.mfgorderid,u.attributevalue,0 sequence,'' workflowstepname,'未排产;未排产;' status
FROM mfgorder mfg
LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename='发动机'
LEFT JOIN enginepartplan ep ON ep.mfgorderid = mfg.mfgorderid
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
--查机型
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
WHERE ep.mfgorderid IS NULL AND trunc(mfg.factorycompletiondate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate)) 
      AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'";
            if (para.ContainsKey("productfamilyname"))
            {
                sql += string.Format(" AND pf.productfamilyname = '{0}' ", para["productfamilyname"]);
            }
            sql += @"

UNION ALL

SELECT temp.*
FROM
(
    SELECT ep.mfgorderid,u.attributevalue,ws.sequence,ws.workflowstepname,
           CASE WHEN MAX(ep.finishdate) IS NOT NULL 
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ' | ' || to_char(MAX(ep.finishdate),'mm/dd') || ';已完工;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) > trunc(MAX(ep.plannedfinishdate))
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';超期未完;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND jj.status = '集件预警'
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';集件预警;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND  ep.exceptiondesc IS NULL
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';已排产无问题;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND ep.exceptiondesc IS NOT NULL
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';已排产有问题;' || ep.exceptiondesc
           END status
    FROM enginepartplan ep
    LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename = '发动机'
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    --查订单类型
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
    LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
    --查机型
    LEFT JOIN productbase pb2 ON pb2.productbaseid = mfg.productbaseid
    LEFT JOIN product p2 ON p2.productid = NVL(pb2.revofrcdid,mfg.productid)
    LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p2.workflowid
    LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
    LEFT JOIN (   ---查询集件状态
                  SELECT m.mfgorderid,m.attributevalue,m.workflowstepname,CASE WHEN SUM(DECODE(m.status,'期量内未发料',1,0)) > 0 THEN '集件预警' ELSE '其他' END status
                  FROM
                  (
                      SELECT t.mfgorderid,t.attributevalue,t.workflowstepname,t.productname,
                             CASE WHEN t.today < t.startdate AND t.isQitao = 1 THEN '提前已发料'
                                  WHEN t.today < t.startdate AND t.isQitao = 0 THEN '提前未发料'
                                  WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 1 THEN '期量内发料'
                                  WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 0 THEN '期量内未发料'
                                  WHEN t.today > t.enddate AND t.isQitao = 1 THEN '超期发料'
                                  WHEN t.today > t.enddate AND t.isQitao = 0 THEN '超期未发料' 
                             END status
                      FROM
                      (
                          SELECT TRUNC((ep.plannedfinishdate - NVL(ep.priordays,1))) startdate,trunc(ep.plannedfinishdate) enddate,trunc(SYSDATE) today,
                                 wfs.workflowstepname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty,
                                 SUM(pl.Qtyrequired) Qtyrequired,CASE WHEN cmm.qty IS NOT NULL THEN 1 ELSE 0 END isQitao
                          FROM enginepartplan ep
                          LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
                          LEFT JOIN mfgorder m ON m.mfgorderid = ep.mfgorderid
                          LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename='发动机'
                          LEFT JOIN bom b ON b.workflowid = ep.workflowid
                          LEFT JOIN productmateriallistitem PL ON pl.Bomid = b.bomid
                          LEFT JOIN productbase pb1 ON pb1.productbaseid = pl.Productbaseid
                          LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,pl.Productid)
                          LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                          LEFT JOIN ( SELECT cm.mfgordername,cm.productname,SUM(NVL(cm.qty,0)) qty 
                                      FROM Collecterpmaterial cm 
                                      WHERE cm.qty > 0 
                                      GROUP BY cm.mfgordername,cm.productname ) cmm ON cmm.mfgordername = m.mfgordername AND cmm.productname = pb.productname
                          WHERE pl.Bigsmallpart = '大零件' AND wfs.sequence < 8 ";

            sql += string.Format(" AND u.attributevalue IN {0} ",sbEngineList.ToString());
                                
            sql += @"
                          GROUP BY ep.plannedfinishdate,ep.priordays,wfs.workflowstepname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty
                      ) t 
                  ) m
                  GROUP BY m.mfgorderid,m.attributevalue,m.workflowstepname
                  --集件状态
              ) jj ON jj.mfgorderid = ep.mfgorderid AND jj.workflowstepname = ws.workflowstepname
    WHERE p.productid = ep.productid AND ws.sequence < 13
          AND trunc(mfg.factorycompletiondate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate))
              AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'";

            if (para.ContainsKey("productfamilyname"))
            {
                sql += string.Format(" AND pf.productfamilyname = '{0}' ", para["productfamilyname"]);
            }

            sql += @"
    GROUP BY ep.mfgorderid,u.attributevalue,ws.sequence,ws.workflowstepname,ep.priordays,ep.exceptiondesc,jj.status
    ORDER BY u.attributevalue,ws.sequence
) temp
";
            DataTable dtQuery = dal.GetDataTable(sql);

            DataTable dtResult = dtQuery.DefaultView.ToTable(true, "attributevalue");

            dtResult.Columns.Add("workflowstepname");
            dtResult.Columns.Add("yicijijian");//一次集件
            dtResult.Columns.Add("yicibuzhuang");//一次部装
            dtResult.Columns.Add("yicichuanzhuang");//一次传装
            dtResult.Columns.Add("yicizongzhuang");//一次总装
            dtResult.Columns.Add("gongchangshiche");//工厂试车
            dtResult.Columns.Add("fenjie");//分解
            dtResult.Columns.Add("gujian");//故检
            dtResult.Columns.Add("ercijijian");//二次集件
            dtResult.Columns.Add("ercibuzhuang");//二次部装
            dtResult.Columns.Add("ercichuanzhuang");//二次传装
            dtResult.Columns.Add("ercizongzhuang");//二次总装
            dtResult.Columns.Add("jianyanshiche");//检验试车

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("attributevalue = '{0}'", dr["attributevalue"].ToString());
                DataRow[] drList = dtQuery.Select(filter);

                dr["workflowstepname"] = "排产日期 | 报工日期";

                if (drList.Length == 1)
                {
                    for (int i = 0; i < 12; i++)
                    {
                        dr[2 + i] = "未排产;未排产";
                    }
                }
                else
                {
                    for (int j = 0; j < drList.Length; j++)
                    {
                        dr[2 + j] = drList[j]["status"];
                    }
                }
            }

            return dtResult;
        }

        //批产、大修、检修、备件(待删)
        public DataTable GetPichanPlanInfoBak(Dictionary<string, string> para)
        {
            string sql = @"
SELECT mfg.mfgorderid,u.attributevalue,0 sequence,'' workflowstepname,'未排产;未排产;' status
FROM mfgorder mfg
LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename='发动机'
LEFT JOIN enginepartplan ep ON ep.mfgorderid = mfg.mfgorderid
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
WHERE ep.mfgorderid IS NULL AND trunc(mfg.factorystardate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate)) ";
            if (para.ContainsKey("ordertypename"))
            {
                sql += string.Format(" AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '{0}' ", para["ordertypename"]);
            }
            sql += @"
UNION ALL
SELECT temp.*
FROM
(
    SELECT ep.mfgorderid,u.attributevalue,ws.sequence,ws.workflowstepname,
           CASE WHEN MAX(ep.finishdate) IS NOT NULL 
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ' | ' || to_char(MAX(ep.finishdate),'mm/dd') || ';已完工;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) > trunc(MAX(ep.plannedfinishdate))
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';超期未完;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND jj.status = '集件预警'
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';集件预警;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND  ep.exceptiondesc IS NULL
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';已排产无问题;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND ep.exceptiondesc IS NOT NULL
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';已排产有问题;' || ep.exceptiondesc
           END status
    FROM enginepartplan ep
    LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename = '发动机'
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    --查订单类型
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
    LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
    LEFT JOIN (   ---查询集件状态
                  SELECT m.mfgorderid,m.attributevalue,m.workflowstepname,CASE WHEN SUM(DECODE(m.status,'期量内未发料',1,0)) > 0 THEN '集件预警' ELSE '其他' END status
                  FROM
                  (
                      SELECT t.mfgorderid,t.attributevalue,t.workflowstepname,t.productname,
                             CASE WHEN t.today < t.startdate AND t.isQitao = 1 THEN '提前已发料'
                                  WHEN t.today < t.startdate AND t.isQitao = 0 THEN '提前未发料'
                                  WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 1 THEN '期量内发料'
                                  WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 0 THEN '期量内未发料'
                                  WHEN t.today > t.enddate AND t.isQitao = 1 THEN '超期发料'
                                  WHEN t.today > t.enddate AND t.isQitao = 0 THEN '超期未发料' 
                             END status
                      FROM
                      (
                          SELECT TRUNC((ep.plannedfinishdate - NVL(ep.priordays,1))) startdate,trunc(ep.plannedfinishdate) enddate,trunc(SYSDATE) today,
                                 wfs.workflowstepname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty,
                                 SUM(pl.Qtyrequired) Qtyrequired,CASE WHEN cmm.qty IS NOT NULL THEN 1 ELSE 0 END isQitao
                          FROM enginepartplan ep
                          LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
                          LEFT JOIN mfgorder m ON m.mfgorderid = ep.mfgorderid
                          LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename='发动机'
                          LEFT JOIN bom b ON b.workflowid = ep.workflowid
                          LEFT JOIN productmateriallistitem PL ON pl.Bomid = b.bomid
                          LEFT JOIN productbase pb1 ON pb1.productbaseid = pl.Productbaseid
                          LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,pl.Productid)
                          LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                          LEFT JOIN ( SELECT cm.mfgordername,cm.productname,SUM(NVL(cm.qty,0)) qty 
                                      FROM Collecterpmaterial cm 
                                      WHERE cm.qty > 0 
                                      GROUP BY cm.mfgordername,cm.productname ) cmm ON cmm.mfgordername = m.mfgordername AND cmm.productname = pb.productname
                          WHERE pl.Bigsmallpart = '大零件'
                                AND wfs.sequence < 8
                                AND u.attributevalue IN 
                                (
                                    SELECT distinct u.attributevalue
                                    FROM mfgorder m 
                                    LEFT JOIN ordertype ot ON m.ordertypeid=ot.ordertypeid
                                    LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid
                                    LEFT JOIN productbase pb ON m.productbaseid=pb.productbaseid
                                    LEFT JOIN product p ON NVL(pb.revofrcdid,m.productid)=p.productid
                                    LEFT JOIN enginepartplan ep ON ep.mfgorderid=m.mfgorderid and ep.productid=p.productid
                                    LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
                                    LEFT JOIN Userattribute u2 ON u2.parentid = m.mfgorderid AND u2.userattributename = '订单类别'
                                    WHERE ep.workflowstepid IS NOT NULL AND u.userattributename='发动机'
                                          AND trunc(m.factorystardate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate)) ";

            if (para.ContainsKey("ordertypename"))
            {
                sql += string.Format(" AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '{0}' ", para["ordertypename"]);
            }

            sql += @"                  
                                )
                          GROUP BY ep.plannedfinishdate,ep.priordays,wfs.workflowstepname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty
                      ) t 
                  ) m
                  GROUP BY m.mfgorderid,m.attributevalue,m.workflowstepname
                  --集件状态
              ) jj ON jj.mfgorderid = ep.mfgorderid AND jj.workflowstepname = ws.workflowstepname
    WHERE p.productid = ep.productid AND ws.sequence < 13
          AND trunc(mfg.factorystardate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate)) ";

            if (para.ContainsKey("ordertypename"))
            {
                sql += string.Format(" AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '{0}' ", para["ordertypename"]);
            }

            sql += @"
    GROUP BY ep.mfgorderid,u.attributevalue,ws.sequence,ws.workflowstepname,ep.priordays,ep.exceptiondesc,jj.status
    ORDER BY u.attributevalue,ws.sequence
) temp
";
            DataTable dtQuery = dal.GetDataTable(sql);

            DataTable dtResult = dtQuery.DefaultView.ToTable(true, "attributevalue");

            dtResult.Columns.Add("workflowstepname");
            dtResult.Columns.Add("yicijijian");//一次集件
            dtResult.Columns.Add("yicibuzhuang");//一次部装
            dtResult.Columns.Add("yicichuanzhuang");//一次传装
            dtResult.Columns.Add("yicizongzhuang");//一次总装
            dtResult.Columns.Add("gongchangshiche");//工厂试车
            dtResult.Columns.Add("fenjie");//分解
            dtResult.Columns.Add("gujian");//故检
            dtResult.Columns.Add("ercijijian");//二次集件
            dtResult.Columns.Add("ercibuzhuang");//二次部装
            dtResult.Columns.Add("ercichuanzhuang");//二次传装
            dtResult.Columns.Add("ercizongzhuang");//二次总装
            dtResult.Columns.Add("jianyanshiche");//检验试车

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("attributevalue = '{0}'", dr["attributevalue"].ToString());
                DataRow[] drList = dtQuery.Select(filter);

                dr["workflowstepname"] = "排产日期 | 报工日期";

                if (drList.Length == 1)
                {
                    for (int i = 0; i < 12; i++)
                    {
                        dr[2 + i] = "未排产;未排产";
                    }
                }
                else
                {
                    for (int j = 0; j < drList.Length; j++)
                    {
                        dr[2 + j] = drList[j]["status"];
                    }
                }
            }

            return dtResult;
        }

        //新机(待删)
        public DataTable GetXinjiPlanInfoOld(Dictionary<string, string> para)
        {
            string sql = @"
SELECT mfg.mfgorderid,u.attributevalue,0 sequence,'' workflowstepname,'未排产;未排产;' status
FROM mfgorder mfg
LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename='发动机'
LEFT JOIN enginepartplan ep ON ep.mfgorderid = mfg.mfgorderid
LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
--查机型
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
WHERE ep.mfgorderid IS NULL AND trunc(mfg.factorystardate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate)) 
      AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'";
            if (para.ContainsKey("productfamilyname"))
            {
                sql += string.Format(" AND pf.productfamilyname = '{0}' ", para["productfamilyname"]);
            }
            sql += @"

UNION ALL

SELECT temp.*
FROM
(
    SELECT ep.mfgorderid,u.attributevalue,ws.sequence,ws.workflowstepname,
           CASE WHEN MAX(ep.finishdate) IS NOT NULL 
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ' | ' || to_char(MAX(ep.finishdate),'mm/dd') || ';已完工;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) > trunc(MAX(ep.plannedfinishdate))
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';超期未完;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND jj.status = '集件预警'
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';集件预警;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND  ep.exceptiondesc IS NULL
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';已排产无问题;' || ep.exceptiondesc
                WHEN MAX(ep.finishdate) IS NULL AND TRUNC(SYSDATE) <= trunc(MAX(ep.plannedfinishdate)) AND ep.exceptiondesc IS NOT NULL
                     THEN to_char(trunc(MAX(ep.plannedfinishdate)),'mm/dd') || ';已排产有问题;' || ep.exceptiondesc
           END status
    FROM enginepartplan ep
    LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename = '发动机'
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    --查订单类型
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
    LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
    --查机型
    LEFT JOIN productbase pb2 ON pb2.productbaseid = mfg.productbaseid
    LEFT JOIN product p2 ON p2.productid = NVL(pb2.revofrcdid,mfg.productid)
    LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p2.workflowid
    LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
    LEFT JOIN (   ---查询集件状态
                  SELECT m.mfgorderid,m.attributevalue,m.workflowstepname,CASE WHEN SUM(DECODE(m.status,'期量内未发料',1,0)) > 0 THEN '集件预警' ELSE '其他' END status
                  FROM
                  (
                      SELECT t.mfgorderid,t.attributevalue,t.workflowstepname,t.productname,
                             CASE WHEN t.today < t.startdate AND t.isQitao = 1 THEN '提前已发料'
                                  WHEN t.today < t.startdate AND t.isQitao = 0 THEN '提前未发料'
                                  WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 1 THEN '期量内发料'
                                  WHEN t.today >= t.startdate AND t.today <= t.enddate AND t.isQitao = 0 THEN '期量内未发料'
                                  WHEN t.today > t.enddate AND t.isQitao = 1 THEN '超期发料'
                                  WHEN t.today > t.enddate AND t.isQitao = 0 THEN '超期未发料' 
                             END status
                      FROM
                      (
                          SELECT TRUNC((ep.plannedfinishdate - NVL(ep.priordays,1))) startdate,trunc(ep.plannedfinishdate) enddate,trunc(SYSDATE) today,
                                 wfs.workflowstepname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty,
                                 SUM(pl.Qtyrequired) Qtyrequired,CASE WHEN cmm.qty IS NOT NULL THEN 1 ELSE 0 END isQitao
                          FROM enginepartplan ep
                          LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
                          LEFT JOIN mfgorder m ON m.mfgorderid = ep.mfgorderid
                          LEFT JOIN Userattribute u ON u.parentid = m.mfgorderid AND u.userattributename='发动机'
                          LEFT JOIN bom b ON b.workflowid = ep.workflowid
                          LEFT JOIN productmateriallistitem PL ON pl.Bomid = b.bomid
                          LEFT JOIN productbase pb1 ON pb1.productbaseid = pl.Productbaseid
                          LEFT JOIN product p ON p.productid = NVL(pb1.revofrcdid,pl.Productid)
                          LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                          LEFT JOIN ( SELECT cm.mfgordername,cm.productname,SUM(NVL(cm.qty,0)) qty 
                                      FROM Collecterpmaterial cm 
                                      WHERE cm.qty > 0 
                                      GROUP BY cm.mfgordername,cm.productname ) cmm ON cmm.mfgordername = m.mfgordername AND cmm.productname = pb.productname
                          WHERE pl.Bigsmallpart = '大零件'
                                AND wfs.sequence < 8
                                AND u.attributevalue IN 
                                (
                                    SELECT distinct u.attributevalue
                                    FROM mfgorder m 
                                    LEFT JOIN ordertype ot ON m.ordertypeid=ot.ordertypeid
                                    LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid
                                    LEFT JOIN productbase pb ON m.productbaseid=pb.productbaseid
                                    LEFT JOIN product p ON NVL(pb.revofrcdid,m.productid)=p.productid
                                    LEFT JOIN enginepartplan ep ON ep.mfgorderid=m.mfgorderid and ep.productid=p.productid
                                    LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
                                    LEFT JOIN Userattribute u2 ON u2.parentid = m.mfgorderid AND u2.userattributename = '订单类别'
                                    LEFT JOIN productbase pb2 ON pb2.productbaseid = m.productbaseid
                                    LEFT JOIN product p2 ON p2.productid = NVL(pb2.revofrcdid,m.productid)
                                    LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p2.workflowid
                                    LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
                                    WHERE ep.workflowstepid IS NOT NULL AND u.userattributename='发动机' 
                                          AND trunc(m.factorystardate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate))
                                          AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'";

            if (para.ContainsKey("productfamilyname"))
            {
                sql += string.Format(" AND pf.productfamilyname = '{0}' ", para["productfamilyname"]);
            }

            sql += @"                  
                                )
                          GROUP BY ep.plannedfinishdate,ep.priordays,wfs.workflowstepname,u.attributevalue,m.mfgordername,ep.mfgorderid,pb.productname,p.description,cmm.qty
                      ) t 
                  ) m
                  GROUP BY m.mfgorderid,m.attributevalue,m.workflowstepname
                  --集件状态
              ) jj ON jj.mfgorderid = ep.mfgorderid AND jj.workflowstepname = ws.workflowstepname
    WHERE p.productid = ep.productid AND ws.sequence < 13
          AND trunc(mfg.factorystardate) BETWEEN trunc(sysdate, 'mm') AND trunc(last_day(sysdate))
              AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'";

            if (para.ContainsKey("productfamilyname"))
            {
                sql += string.Format(" AND pf.productfamilyname = '{0}' ", para["productfamilyname"]);
            }

            sql += @"
    GROUP BY ep.mfgorderid,u.attributevalue,ws.sequence,ws.workflowstepname,ep.priordays,ep.exceptiondesc,jj.status
    ORDER BY u.attributevalue,ws.sequence
) temp
";
            DataTable dtQuery = dal.GetDataTable(sql);

            DataTable dtResult = dtQuery.DefaultView.ToTable(true, "attributevalue");

            dtResult.Columns.Add("workflowstepname");
            dtResult.Columns.Add("yicijijian");//一次集件
            dtResult.Columns.Add("yicibuzhuang");//一次部装
            dtResult.Columns.Add("yicichuanzhuang");//一次传装
            dtResult.Columns.Add("yicizongzhuang");//一次总装
            dtResult.Columns.Add("gongchangshiche");//工厂试车
            dtResult.Columns.Add("fenjie");//分解
            dtResult.Columns.Add("gujian");//故检
            dtResult.Columns.Add("ercijijian");//二次集件
            dtResult.Columns.Add("ercibuzhuang");//二次部装
            dtResult.Columns.Add("ercichuanzhuang");//二次传装
            dtResult.Columns.Add("ercizongzhuang");//二次总装
            dtResult.Columns.Add("jianyanshiche");//检验试车

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("attributevalue = '{0}'", dr["attributevalue"].ToString());
                DataRow[] drList = dtQuery.Select(filter);

                dr["workflowstepname"] = "排产日期 | 报工日期";

                if (drList.Length == 1)
                {
                    for (int i = 0; i < 12; i++)
                    {
                        dr[2 + i] = "未排产;未排产";
                    }
                }
                else
                {
                    for (int j = 0; j < drList.Length; j++)
                    {
                        dr[2 + j] = drList[j]["status"];
                    }
                }
            }

            return dtResult;
        }
    }
}
